﻿Partial Public Class SelectSeat

    Inherits System.Web.UI.Page
    Dim Flight_Number As Integer
    Dim User_Id As Integer



    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        btnContinue.PostBackUrl = "Summery.aspx"

        If txtFlightNumber.Text = "" Then
            txtFlightNumber.Text = Request("ctl00$MainContent$txtFlightNum")

        End If

        If txtUserId.Text = "" Then
            txtUserId.Text = Request("ctl00$MainContent$txtUserId")

        End If

        Flight_Number = Convert.ToInt32(txtFlightNumber.Text)
        User_Id = Convert.ToInt32(txtUserId.Text)
        'Flight_Number = 2
        'User_Id = 1

        If Flight_Number = 0 Then
            Flight_Number = 1
        End If

        checkSEATS()
    End Sub

    Function CreateDataSet(ByVal srtSql As String) As DataSet
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand
        Dim dtAdapter As New MySql.Data.MySqlClient.MySqlDataAdapter

        Dim ds As New DataSet

        Dim strConnString

        strConnString = "Server=localhost;User Id=goldair_dapp; Password=geckodev;Database=goldair_db; Pooling=false"


        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = srtSql
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd

        Try
            dtAdapter.Fill(ds, "aircraft")
        Catch ex As Exception

        End Try

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing

        Return ds

    End Function

    Sub ExecuteQuery(ByVal srtSql As String)
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand
        Dim dtAdapter As New MySql.Data.MySqlClient.MySqlDataAdapter

        Dim ds As New DataSet

        Dim strConnString

        strConnString = "Server=localhost;User Id=goldair_dapp; Password=geckodev; Database=goldair_db; Pooling=false"

        objConn.ConnectionString = strConnString
        objConn.Open()

        With objCmd
            .Connection = objConn
            .CommandText = srtSql
            .CommandType = CommandType.Text
        End With

        objCmd.ExecuteNonQuery()

        objConn.Close()
        objConn = Nothing



    End Sub


    Function seatCHK1A() As String
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(0)(2).ToString()
        seatNum = ds.Tables(0).Rows(0)(1).ToString()
        If seat = "1" Then
            Return ("Seat " + seatNum + " <br> Currently: Available")
        Else
            Return ("Seat " + seatNum + " <br> Currently: Unavailable")
        End If

    End Function

    Sub seatCHK2A()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(1)(2).ToString()
        seatNum = ds.Tables(0).Rows(1)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK3A()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(2)(2).ToString()
        seatNum = ds.Tables(0).Rows(2)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK3B()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(3)(2).ToString()
        seatNum = ds.Tables(0).Rows(3)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK3C()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(4)(2).ToString()
        seatNum = ds.Tables(0).Rows(4)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK4A()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(5)(2).ToString()
        seatNum = ds.Tables(0).Rows(5)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK4B()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(6)(2).ToString()
        seatNum = ds.Tables(0).Rows(6)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Sub seatCHK4C()
        Dim seat, seatNum
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(7)(2).ToString()
        seatNum = ds.Tables(0).Rows(7)(1).ToString()
        If seat = "1" Then
            Response.Write("Seat " + seatNum + " <br> Currently: Available")
        Else
            Response.Write("Seat " + seatNum + " <br> Currently: Unavailable")
        End If
    End Sub

    Protected Sub Button_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(0)(2).ToString()
        seatNum = ds.Tables(0).Rows(0)(1).ToString()

        If seat = "1" Then
            clearSeats()
            strMessage = "Seat 1A has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id`=" & Flight_Number & " AND`seats`.`seat`='1A' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "1A"

            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")

        Else
            strMessage = "Seat 1A is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(1)(2).ToString()
        seatNum = ds.Tables(0).Rows(1)(1).ToString()

        If seat = "1" Then
            clearSeats()
            strMessage = "Seat 2A has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id`=" & Flight_Number & " AND`seats`.`seat`='2A' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "2A"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 2A is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(2)(2).ToString()
        seatNum = ds.Tables(0).Rows(2)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 3A has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id`=" & Flight_Number & " AND`seats`.`seat`='3A' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "3A"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 3A is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(3)(2).ToString()
        seatNum = ds.Tables(0).Rows(3)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 3B has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id`=" & Flight_Number & " AND`seats`.`seat`='3B' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "3B"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 3B is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(4)(2).ToString()
        seatNum = ds.Tables(0).Rows(4)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 3C has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id` =" & Flight_Number & " AND `seats`.`seat`='3C' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "3C"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")

        Else
            strMessage = "Seat 3C is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button5_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button5.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(5)(2).ToString()
        seatNum = ds.Tables(0).Rows(5)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 4A has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id` =" & Flight_Number & " AND `seats`.`seat`='4A' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "4A"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 4A is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button6_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button6.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(6)(2).ToString()
        seatNum = ds.Tables(0).Rows(6)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 4B has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id` =" & Flight_Number & " AND `seats`.`seat`='4B' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "4B"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 4B is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click

        Dim seat, seatNum, strMessage
        Dim ds As DataSet

        ds = CreateDataSet("SELECT * FROM seats where flight_id=" & Flight_Number)
        seat = ds.Tables(0).Rows(7)(2).ToString()
        seatNum = ds.Tables(0).Rows(7)(1).ToString()

        If seat = "1" Then
            strMessage = "Seat 4C has been selected."
            Dim ds2 As DataSet
            ds2 = CreateDataSet("UPDATE`goldair_db`.`seats`SET`availability`='0' WHERE`seats`.`flight_id` =" & Flight_Number & " AND `seats`.`seat`='4C' AND`seats`.`availability`=1 LIMIT 1 ;")
            txtSeat.Text = "4C"
            ExecuteQuery("UPDATE user_flight SET num_seat='" & txtSeat.Text & "' WHERE flight_id =" & Flight_Number & " AND user_id=" & User_Id & "")
        Else
            strMessage = "Seat 4C is already taken. Please select another seat."
        End If

        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If

    End Sub

    Sub checkSEATS()
        Dim ds As DataSet
        Dim sqlResult, strMessage
        ds = CreateDataSet("SELECT SUM(availability) FROM seats where flight_id=" & Flight_Number)
        sqlResult = ds.Tables(0).Rows(0)(0).ToString()

        If sqlResult = "0" Then
            strMessage = "All seats are taken."
            Dim strScript As String = "<script language=JavaScript>"
            strScript += "alert(""" & strMessage & """);"
            strScript += "</script>"
            If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
                ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
            End If
        End If
    End Sub

    Protected Sub Button8_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button8.Click
        clearSeats()
        Dim strMessage
        strMessage = "All seats have been cleared"
        Dim strScript As String = "<script language=JavaScript>"
        strScript += "alert(""" & strMessage & """);"
        strScript += "</script>"
        If (Not ClientScript.IsStartupScriptRegistered("clientScript")) Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "clientScript", strScript)
        End If
    End Sub

    Sub clearSeats()
        Dim ds As DataSet
        ds = CreateDataSet("UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`flight_id` =" & Flight_Number & " AND `seats`.`seat` = '1A' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '2A' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '3A' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '3B' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '3C' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '4A' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '4B' AND `seats`.`availability` = 0 LIMIT 1; UPDATE `goldair_db`.`seats` SET `availability` = '1' WHERE `seats`.`aircraft_id` = 1 AND `seats`.`seat` = '4C' AND `seats`.`availability` = 0 LIMIT 1;")
    End Sub

End Class



